﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data.SQLite;
using System.Data;
using System.Windows.Forms;

namespace StockSBIE
{
    public class StockSBDetailManager
    {

        public class SBDetial
        {
            public int 序列 { get; set; }
            public String 券商 { get; set; }
            public decimal 成交單價 { get; set; }
            public int 買進股數 { get; set; }
            public int 賣出股數 { get; set; }
        }
        public List<SBDetial> getSBDetails(String code, DateTime d)
        {
            List<SBDetial> result = new List<SBDetial>();
            String sql = "SELECT  SBDetails.seq, Insts.InstCode, Insts.InstName, SBDetails.price, SBDetails.buy, SBDetails.sell " +
                   "FROM      SBDetails INNER JOIN  SBDataTask  " +
                   "ON SBDetails.taskId = SBDataTask.SBId  " +
                   "INNER JOIN  Insts ON SBDetails.instId = Insts.InstId " +
                   "where   (SBDataTask.日期 = ?) and (SBDataTask.StockCode = ?)  order by SBDetails.seq";


            SQLiteCommand cmd = SQLiteAgent.getCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add(SQLiteAgent.createParam(DateUtils.getSlashYYYYMMDD(d)));
            cmd.Parameters.Add(SQLiteAgent.createParam(code));
            SQLiteDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                SBDetial r = new SBDetial();
                r.序列 = reader.GetInt32(0);
                r.券商 = reader.GetString(1) + " " + reader.GetString(2);
                r.成交單價 = reader.GetDecimal(3);
                r.買進股數 = reader.GetInt32(4);
                r.賣出股數 = reader.GetInt32(5);
                result.Add(r);
            }
            return result;
        }
        public enum SBTaskState : int
        {
            停止 = 0,
            待處理 = 1,
            抓取中 = 2,
            已抓取 = 3,
            完成 = 4,
            無資料 = 5,
            錯誤 = -1,

        }
        Hashtable InstCodes = new Hashtable();
        public int setErrorTaskToProcessWaiting()
        {
            String sql = "update SBDataTask set state =1 where state =-1";
            SQLiteCommand cmd = SQLiteAgent.getCommand();
            cmd.CommandText = sql;
            return cmd.ExecuteNonQuery();
        }
        public static void updateTaskState(Guid taskId, SBTaskState state, DateTime d)
        {
            String sql = "update SBDataTask set  state =?, lastprocessTime = ?  where SBID= ?";
            int s = (int)state;
            SQLiteCommand cmd = SQLiteAgent.getCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add(SQLiteAgent.createParam(s));

            SQLiteParameter dp = new SQLiteParameter(System.Data.DbType.DateTime);
            dp.Value = d;
            cmd.Parameters.Add(dp);
            SQLiteParameter p = new SQLiteParameter();
            p.Value = taskId.ToString("B");
            cmd.Parameters.Add(p);
            int cc = cmd.ExecuteNonQuery();
            cmd.Dispose();
        }
        public class SBRecordState
        {
            public String 代號 { get; set; }
            public SBTaskState 處理進度 { get; set; }
            public String 處理時間 { get; set; }
            public String 名稱 { get; set; }
            public decimal 股價 { get; set; }
            public int 交易筆數 { get; set; }
            public decimal 交易金額 { get; set; }
            public Guid taskId = Guid.Empty;
            public int codeLen = 0;
            public int 成交股數;
        }


        /*********************************************************************
         * 
         * 語法有問題要改
         * 
         * ********************************************************************/
        public List<SBRecordState> getCurrentSBStates(DateTime d)
        {

            List<SBRecordState> result = new List<SBRecordState>();

            String sql = "SELECT  SBDataTask.StockCode, SBDataTask.state, SBDataTask.lastprocessTime, SBDataTask.SBId, SBDataTask.日期, StockCodes.stockName    " +
                                    "FROM      SBDataTask INNER JOIN    StockCodes ON SBDataTask.StockCode = StockCodes.stockCode " +
                                    " WHERE   ( SBDataTask.日期 = ?)";

            SQLiteCommand cmd = SQLiteAgent.getCommand();
            cmd.CommandText = sql;
            SQLiteParameter pd = new SQLiteParameter();
            pd.Value = DateUtils.getSlashYYYYMMDD(d);
            cmd.Parameters.Add(pd);
            SQLiteDataReader reader = cmd.ExecuteReader();
            Hashtable ht = new Hashtable();

            while (reader.Read())
            {
                SBRecordState state = new SBRecordState();
                state.代號 = reader.GetString(0);
                String ss = reader.GetValue(1).ToString();
                int i;
                if (int.TryParse(ss, out i))
                {
                    state.處理進度 = (SBTaskState)i;
                }
                else
                {
                    state.處理進度 = SBTaskState.待處理;
                }
                //------------
                ss = reader.GetValue(2).ToString();
                DateTime pdd = DateTime.Now;
                if (DateTime.TryParse(ss, out  pdd))
                {
                    state.處理時間 = DateUtils.getYYYYMMDDHHSS(pdd);
                }
                else
                    state.處理時間 = "";
                state.taskId = reader.GetGuid(3);
                state.名稱 = reader.GetString(5);
                ht.Add(state.代號, state);
                result.Add(state);
            }
            reader.Close();
            cmd.Dispose();

            //------------------------------------------------------------------------------------
            try
            {
            String sql2 = "SELECT  證券代號, 成交股數, 成交筆數, 成交金額, 收盤價, 日期 " +
                                  "FROM      StockDaily where 日期 = ? ";

            
            cmd = SQLiteAgent.getCommand();
            cmd.CommandText = sql2;

            pd = new SQLiteParameter();
            pd.Value = DateUtils.getSlashYYYYMMDD(d);
            cmd.Parameters.Add(pd);


                reader = cmd.ExecuteReader();

                decimal localdec=new decimal(0);
                while (reader.Read())
                {
                    String code = reader.GetString(0);
                    if (ht.Contains(code) == false)
                        continue;
                    SBRecordState state = (SBRecordState)ht[code];
                    state.成交股數 = reader.GetInt32(1);
                    state.交易筆數 = reader.GetInt32(2);
                    state.交易金額 = reader.GetDecimal(3);
                    String ps = reader.GetValue(4).ToString();

                    //當沒有交易的時候 股數 比數 金額 會=0 但是股價是空
                    //state.股價 = reader.GetDecimal(4);
                    if (decimal.TryParse(ps, out localdec))
                    {
                        state.股價 = localdec;
                    }

                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\r\n" + e.StackTrace);
            }
            return result;
        }

        protected void createSBTask(List<String> codes, String dateStr)
        {

            String sql = "Insert into SBDataTask ([日期],stockCode,SBId) values (?,?,?)";

            SQLiteCommand cmd = SQLiteAgent.getCommand();
            foreach (String code in codes)
            {
                cmd.CommandText = sql;
                cmd.Parameters.Clear();
                cmd.Parameters.Add(SQLiteAgent.createParam(dateStr));
                cmd.Parameters.Add(SQLiteAgent.createParam(code));
                cmd.Parameters.Add(SQLiteAgent.createParam(NewGUID.create("sbtk").ToString("B")));
                cmd.ExecuteNonQuery();
            }
            cmd.Dispose();
        }

        public void prepareSBTask(DateTime d)
        {
            String dateStr = DateUtils.getSlashYYYYMMDD(d);
            String sql = "select StockCode from SBDataTask where 日期= ?";
            SQLiteCommand cmd = SQLiteAgent.getCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add(SQLiteAgent.createParam(dateStr));
            SQLiteDataReader rdr = cmd.ExecuteReader();

            HashSet<String> createdCodes = new HashSet<string>();
            while (rdr.Read())
            {
                String code = rdr.GetString(0);
                createdCodes.Add(code);
            }
            rdr.Close();
            cmd.Dispose();
            //-----------------------------------------------------
            //找出當日的 有交易的 ,Order之.並且Create.
            cmd = SQLiteAgent.getCommand();
            sql = "SELECT [證券代號] , 成交股數 FROM StockDaily where 日期 = ?  order by 成交筆數 desc; ";
            cmd.CommandText = sql;
            cmd.Parameters.Add(SQLiteAgent.createParam(dateStr));
            HashSet<String> toCreate = new HashSet<string>();
            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                String code = rdr.GetString(0);
                int num = rdr.GetInt32(1);
                
                if (createdCodes.Contains(code) == false)
                {
                    toCreate.Add(code);
                }
            }
            rdr.Close();
            cmd.Dispose();

            List<String> toCreateL = new List<string>();
            if (toCreate.Count > 0)
            {
                toCreateL.AddRange(toCreate);
                createSBTask(toCreateL, dateStr);
            }
            toCreate.Clear();
            toCreateL.Clear();
            createdCodes.Clear();
        }


        /*************************************************************
         * 
         * 
         * ************************************************************/
        public bool getInstId(String code, out Guid result)
        {
            if (InstCodes.Contains(code))
            {
                Hashtable ht = (Hashtable)InstCodes[code];
                result = (Guid)ht["id"];
                return true;
            }
            result = Guid.Empty;
            return false;
        }

        public Guid registerInst(String code, String name)
        {
            if (InstCodes.Contains(code) == false)
            {
                ArrayList al = new ArrayList();
                Hashtable ht = new Hashtable();
                ht["code"] = code;
                ht["name"] = name;
                Guid id = NewGUID.create("inst");
                ht["id"] = id;
                InstCodes.Add(code, ht);
                ArrayList ps = new ArrayList();
                ps.Add(id.ToString("B"));
                ps.Add(code);
                ps.Add(name);
                String sql = "insert into Insts (InstId,InstCode,InstName) values (?,?,?)";
                SQLiteCommand cmd = SQLiteAgent.getCommand();


                SQLiteAgent.executeInsert(sql, ps);
                return id;
            }
            else
            {
                Guid r = Guid.Empty;
                getInstId(code, out r);
                return r;
            }
        }
        public void preloadInst()
        {
            InstCodes.Clear();
            SQLiteCommand cmd = SQLiteAgent.getCommand();
            cmd.CommandText = "select * from Insts";
            SQLiteDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                String code = rdr.GetString(1);
                String name = rdr.GetString(2);
                String ids = rdr.GetString(0);
                Guid id = Guid.Parse(ids);
                Hashtable ht = new Hashtable();
                ht["code"] = code;
                ht["name"] = name;
                ht["id"] = id;
                InstCodes.Add(code, ht);
            }
            rdr.Close();
            cmd.Dispose();
        }

        protected void deleteSBDetails(Guid taskId)
        {
            //先刪除
            String sql = "delete from SBDetails where taskId=?";
            SQLiteCommand cmd = SQLiteAgent.getCommand();
            cmd.CommandText = sql;
            SQLiteParameter p = new SQLiteParameter(DbType.Guid);
            p.Value = taskId;
            cmd.Parameters.Add(p);
            int deleted = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            cmd.Dispose();

        }
        /****************************************************************
         * 
         * 
         ****************************************************************/
        public void updateSBDetails(List<SBEntry> SBEntries, Guid taskId)
        {
            if (SBEntries == null)
                return;
            if (SBEntries.Count == 0)
                return;
            deleteSBDetails(taskId);

            //更新機構
            foreach (SBEntry e in SBEntries)
            {
                Guid instId = Guid.Empty;
                if (getInstId(e.instCode, out instId))
                    e.instId = instId;
                else
                    e.instId = registerInst(e.instCode, e.instName);
            }
            //---->
            InsertSBDetails(SBEntries, taskId);

        }
        protected void InsertSBDetails(List<SBEntry> SBEntries, Guid taskId)
        {
            int step = 10;
            /*
            if (SBEntries.Count > step)
            {
                int sz=SBEntries.Count;
                for(int i=0;i<sz;i+=step)
                {
                    if (i + step > sz)
                    {
                        InsertSBDetails(SBEntries.GetRange(i, sz - i), taskId);
                    }
                    else
                    {
                        InsertSBDetails(SBEntries.GetRange(i, step), taskId);
                    }
                }
            } */

            //-----------組合
            SQLiteTransaction transaction = SQLiteAgent.BeginTransation();
            SQLiteCommand cmd = SQLiteAgent.getCommand();
            cmd.CommandText = "Insert into SBDetails ( taskId,seq,instId,price,buy,sell) values (?,?,?,?,?,?)";
            SQLiteParameter taskIdP = new SQLiteParameter();
            SQLiteParameter seqP = new SQLiteParameter(DbType.Int32);
            SQLiteParameter instIdP = new SQLiteParameter();
            SQLiteParameter priceP = new SQLiteParameter(DbType.Decimal);
            SQLiteParameter buyP = new SQLiteParameter(DbType.Int32);
            SQLiteParameter sellP = new SQLiteParameter(DbType.Int32);
            cmd.Parameters.Add(taskIdP);
            cmd.Parameters.Add(seqP);
            cmd.Parameters.Add(instIdP);
            cmd.Parameters.Add(priceP);
            cmd.Parameters.Add(buyP);
            cmd.Parameters.Add(sellP);
            cmd.Parameters[0].Value = taskId.ToString("B");
            foreach (SBEntry sb in SBEntries)
            {
                cmd.Parameters[1].Value = sb.seq;
                cmd.Parameters[2].Value = sb.instId.ToString("B");
                cmd.Parameters[3].Value = sb.price;
                cmd.Parameters[4].Value = sb.buy;
                cmd.Parameters[5].Value = sb.sell;
                cmd.ExecuteNonQuery();
            }
            transaction.Commit();
            cmd.Dispose();
        }
    }
}
